#!/usr/bin/perl

use strict;
use DBI;

###
### Phase 0: General parameters
###


# Open the database with DBI
my $dbfile = $ARGV[0] or die("Unable to open database; none provided");
my $db = DBI->connect("dbi:SQLite:$dbfile") or die("Unable to open database");

# Gnuplot global parameters
my $TYPE = "pdf";

# File structure
# ./plot/<time>/ - end plot images
# ./plot/<time>/data/ - source data
my $PLOTPATH = "./plot";
my $DATAPATH = "data";
my $timestamp = time();
my $PLOTDIR = "$PLOTPATH/$timestamp/";
my $DATADIR = "$PLOTPATH/$timestamp/$DATAPATH/";
mkdir "./plot" unless (-e "./plot");
mkdir $PLOTDIR unless (-e $PLOTDIR);
mkdir $DATADIR unless (-e $DATADIR);

# Host name lookup utility, based on /32 
my $NAMEFILE = "target-names.txt";
sub findname($) {
    my $addr = shift;
    $addr =~ s/(^[^:]*:[^:]*:[^:]*:).*/$1/;
    my $name = `grep "$addr" $NAMEFILE | head -n 1`;
    chomp $name;
    $name =~ s/\|.*//;
    return $name || "unknown";
}

# Cache destinations by address and name
my @dsts = ();
my %dstnames = (); # We'll cache names as we find them

###
### Phase 1: Generate avg/min/max for all destinations
###

# Parameters
my $SHIFT = .2;
my $PENALTY_THRESHOLD = 10;
my $STAT_QUERY = <<EOF
select P1.dst, P1.avg6, P1.min6, P1.max6, P2.avg64, P2.min64, P2.max64
from
    (select dst, min(rtt) as min6, max(rtt) as max6, avg(rtt) as avg6 
     from ping64 where (src not like "2002%") group by dst) as P1
    INNER JOIN
    (select dst, min(rtt) as min64, max(rtt) as max64, avg(rtt) as avg64 
     from ping64 where (src like "2002%") group by dst) as P2
    ON P1.dst = P2.dst;
EOF
;
$STAT_QUERY =~ s/\n/ /g;

# Set relevant filenames
my $datafile = "$DATADIR/avg.dat";
my $plotfile = "$PLOTDIR/avg.$TYPE";

# Convert SQLite data to gnuplot format
my $nlines = 0;
my $penalty;
my $selectpenalty;
my $selectcount = 0;
my $sq = $db->prepare($STAT_QUERY);
my ($dst, $avg6, $min6, $max6, $avg64, $min64, $max64);

$sq->execute();
$sq->bind_columns( \$dst, \$avg6, \$min6, \$max6, \$avg64, \$min64, \$max64);

open(DATAOUT, ">$datafile"); 
while ($sq->fetchrow_arrayref) {
    # Cache identifiers for this destination
    push @dsts, $dst;
    $dstnames{$dst} = findname($dst);
    my $name = $dstnames{$dst};

    # Collect statistics
    ++$nlines;
    # Convert seconds to ms
    $avg6 *= 1000;
    $min6 *= 1000;
    $max6 *= 1000;
    $avg64 *= 1000;
    $min64 *= 1000;
    $max64 *= 1000;

    # Accumulate latency penalty
    $penalty += ($avg64 - $avg6);
    if ($avg64 - $avg6 > $PENALTY_THRESHOLD) {
        $selectpenalty += ($avg64 - $avg6);
        $selectcount++;
    }

    # Shift the 6to4 values over by $SHIFT
    my $x64 = $nlines + $SHIFT;
    my $dataline = "$nlines $avg6 $min6 $max6 $x64 $avg64 $min64 $max64 $dst $name \n";
    print DATAOUT $dataline;
}
# Turn accumulated penalties into an average and round to 100ths;
$penalty /= $nlines;
$penalty = int($penalty * 100) / 100;
$selectpenalty /= $selectcount unless ($selectcount == 0);
$selectpenalty = int($selectpenalty * 100) / 100;
close(DATAOUT);

# Plot with gnuplot
my $xmax = $nlines + .5;
open(GNUPLOT, "| gnuplot");
print GNUPLOT <<EOF
set terminal $TYPE 
set output "$plotfile" 
set title "Mean, min, and max RTTs by target"
set xtics rotate by -45 
set xrange [.5:$xmax] 
set xlabel "Ping Target" 
set ylabel "RTT (ms)" 
set label "Average latency penalty: $penalty ms" at graph 0.05, graph .9 
set label "Average significant penalty: $selectpenalty ms ($selectcount/$nlines hosts)" at graph 0.05, graph .8 
plot "$datafile" using 1:2:3:4:xticlabels(10) title "native" with yerrorbars lw 2 pt 2, \\
     "$datafile" using 5:6:7:8 title "6to4 (one-way)" with yerrorbars lw 2 pt 2

EOF
;
close(GNUPLOT);


###
### Phase 2: Generate per-destination time series
###
my $DEST_QUERY = "select distinct dst from ping64 where (dst like '%:%');";
my $NATIVE_QUERY = "select time, rtt from ping64 where (dst like ? and src not like '2002%');";
my $SIXFOUR_QUERY = "select time, rtt from ping64 where (dst like ? and src like '2002%');";
my $ONE_HOUR = 3600;
my $nq = $db->prepare($NATIVE_QUERY);
my $sfq = $db->prepare($SIXFOUR_QUERY);

# For each destination, generate a time series plot
foreach my $dst (@dsts) {
    # Pull the data sets
    $nq->execute($dst);
    $sfq->execute($dst);
    my @nts = @{ $nq->fetchall_arrayref() };
    my @sfts = @{ $sfq->fetchall_arrayref() };

    # Scale data and write to a file
    my $datafile = "$DATADIR/$dst.dat"; $datafile =~ s/:/./g;
    my ($mintime, $maxtime);
    open(DATAOUT, ">$datafile");
    for (my $i=0; $i < scalar(@nts); ++$i) {
        if (!$maxtime || $maxtime < $nts[$i]->[0]) { $maxtime = $nts[$i]->[0]; }
        if (!$mintime || $mintime > $nts[$i]->[0]) { $mintime = $nts[$i]->[0]; }
        print DATAOUT $nts[$i]->[0] ." ". (1000*$nts[$i]->[1]) ."\n";
    }
    print DATAOUT "\n\n";
    for (my $i=0; $i < scalar(@sfts); ++$i) {
        if (!$maxtime || $maxtime < $sfts[$i]->[0]) { $maxtime = $sfts[$i]->[0]; }
        if (!$mintime || $mintime > $sfts[$i]->[0]) { $mintime = $sfts[$i]->[0]; }
        print DATAOUT $sfts[$i]->[0] ." ". (1000*$sfts[$i]->[1]) ."\n";
    }
    close(DATAOUT);

    # Generate time tics to go on the horizontal axis
    my $duration = $maxtime - $mintime;
    my $interval;
    # Compute a nice tic interval based on the duration in the plot
    if ($duration < 3600) { $interval = 120; } # hour => 2-minute tics
    elsif ($duration < 60*60*24) { $interval = 60*60; } # day => hour tics
    elsif ($duration < 7*60*60*24) { $interval = 6*60*60; } # week => 6-hour tics
    else { $interval = 24*60*60; } # More than a week => daily tics
    # Round up to next tic from minimum
    my $hour = int($mintime / $interval)*$interval + $interval;
    my @xtics = ();
    for (; $hour < $maxtime; $hour += $interval) {
        # Tic values are HH:MM:SS
        my $ticvalue = sprintf "%02d:%02d:%02d", ($hour/3600)%24, ($hour/60)%60, $hour%60;
        push @xtics, qq("$ticvalue" $hour);
    }
    my $xticstring = "(". join(", ", @xtics) .")";

    # Plot the data file
    my $name = $dstnames{$dst};
    my $plotfile = "$PLOTDIR/$dst.$TYPE"; $plotfile =~ s/:/./g;
    open(GNUPLOT, "| gnuplot");
    print GNUPLOT <<EOF
set terminal $TYPE
set output "$plotfile"
set xtics $xticstring rotate by -45
set xlabel "Time of ping"
set ylabel "RTT (ms)"
set title "RTT to $dst ($name)"
plot "$datafile" using 1:2 index 0 title "native", "$datafile" using 1:2 index 1 title "6to4 reverse";
EOF
;
    close(GNUPLOT);
}

###
### Phase 3: Merge everything into one PDF
###

### XXX: May not work if $TYPE ne "pdf"

my $REPORTFILE = "$PLOTDIR/report.$TYPE";
system("gs -q -dNOPAUSE -dBATCH -sDEVICE=pdfwrite -sOutputFile=$REPORTFILE $PLOTDIR/*");





